<?php
/**
 * Created by PhpStorm.
 * User: JM003
 * Date: 2020/10/16
 * Time: 15:22
 */
namespace app\admin\controller;
use app\admin\Common\Getpinyin;
use app\admin\model\UploadfileModel;
use think\Db;
class Importexcel extends Base
{
    /**
     * 无表导入excel
     * @return array
     */
    public function getFile(){
        ini_set('memory_limit', '-1');
        require_once(EXTEND_PATH.'/PHPExcel/Classes/PHPExcel.php');
        setlocale(LC_ALL, 'zh_CN.UTF-8');//解决 pathinfo 中文乱码的问题
        $start = microtime(true);//开启代码运行时间
        $extlist = 'xlsx,xls,csv';
        if (request()->isAjax()) {
            $file = request()->file('file');
            $oldFileinfo = $file->getInfo();
            $oldFileNameinfo =$oldFileinfo['name'];
            $fileinfo = pathinfo($oldFileNameinfo);
            $fileExt = $fileinfo['extension'];   //文件扩展
            $basename = $fileinfo['basename'];   //带扩展的文件名
            $oldfilename = $fileinfo['filename'];//不带扩展文件名
            $getpinyin = new Getpinyin();
            $py = $getpinyin ->getPY($oldfilename);
            $prefix = config('database.prefix');    //数据库表扩展名
            $excdatatable = $prefix."uploadfiledata_$py";//创建数据库表名
            $where = "isdelete=0 and filetablename='$excdatatable'";
            $oldData = Db::query("SHOW TABLES LIKE '%$excdatatable%';");//查询是否存在数据表
            if($oldData){
                //有，则删除老数据表
                $deltablesql = "drop table $excdatatable";
                $delresult = Db::execute($deltablesql);
                if($delresult ===false){
                    return msg('-1', '', '上传失败：删除原表失败');
                }
            }
            //echo $oldData;die;
            $info = $file->validate(['ext' =>$extlist ])->move(ROOT_PATH . 'public' . DS . 'upload'.'/'.'excle');
            if ($info) {
                $path =  './upload' .'/'.'excle'. '/' . date('Ymd') . '/' . $info->getFilename();//文件保存路径
                if(empty($path) OR !file_exists($path)) {
                    //die('file not exists!');
                    return msg('-1', '', '上传失败：file not exists!');
                }
                if ($fileExt=='xlsx') {
                    $type='Excel2007';
                }elseif($fileExt=='xls') {
                    $type = 'Excel5';
                }else{
                    $type = 'CSV';
                }
                //echo $fileExt;die;
                $objReader = \PHPExcel_IOFactory::createReader($type);//判断使用哪种格式
                if(!$objReader->canRead($path)){//判断是否是其他文档修改后缀的excel
                    die('No Excel!');
                }
                $objReader ->setReadDataOnly(true); //只读取数据,会智能忽略所有空白行,这点很重要.
                if( $fileExt=='xlsx' || $fileExt=='xls'){
                    $objPHPExcel = $objReader->load($path);
//                    $sheetCount = $objPHPExcel->getSheetCount();//获取sheet工作表总个数
//
//                    if($sheetCount>1){
//                        $RowNum = 0;
//                        /*读取表格数据*/
//                        for($i =0; $i < $sheetCount;$i++){//循环sheet工作表的总个数
//                            $sheet = $objPHPExcel->getSheet($i);
//                            $highestRow = $sheet->getHighestRow();//每个工作表的总行数
//
//                            $RowNum += $highestRow-1;//计算所有sheet的总行数
//                            $highestColumn = $sheet->getHighestColumn();//取得最大的列号
//                            //从第$i个sheet的第1行开始获取数据
//                            for($row = 1;$row <= $highestRow;$row++){
//                                //把每个sheet作为一个新的数组元素 键名以sheet的索引命名 利于后期数组的提取
//
//                                $data[$i] = $objPHPExcel->getSheet($i)->toArray();
//                            }
//                        }
//
//                        //dump($rowData);die;
//                    }else{
//                        $data[] = $objPHPExcel->getSheet()->toArray();
//                    }
                    $data = $objPHPExcel->getSheet()->toArray();
                }
                elseif($fileExt=='csv') {
                    $objReader =$objReader->setDelimiter(',')
                        ->setInputEncoding('GBK')
                        ->setEnclosure('"')
                        ->setLineEnding("\r\n")
                        ->setSheetIndex(0);
                    $objPHPExcel = $objReader->load($path);
                    $data = $objPHPExcel->getSheet()->toArray();
                }
                //array_shift($data);//去掉第一行表头数据
                if($data){
                    $header = $data[0];
                    $headerArray = [];
                    Db::startTrans();
                    $sql = "CREATE TABLE $excdatatable (";
                    $sql.= "id int(11) NOT NULL AUTO_INCREMENT,";
                    foreach ($header as $k=>$v){
                        $field = \PHPExcel_Cell::stringFromColumnIndex($k);//$K 转换成 0->A
                        $Colname[] = \PHPExcel_Cell::stringFromColumnIndex($k);//$K 转换成 0->A
                        $name = trim($v);
                        $sql.= "`$field` varchar(255) DEFAULT NULL COMMENT '$name',";
                        $headerArray[$field] = $name;
                    }
                   // dump($headerArray);die;
                    $sql .= "PRIMARY KEY ( `id` )";
                    $sql .= ") ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='$oldfilename 表';";//根据excel表名称创建数据表
                    $result = Db::execute($sql);
                    if($result !==false){
                        $inst['filetablename'] = $excdatatable;
                        $inst['filepath'] = $path;
                        $inst['oldfilename'] = $basename;
                        $inst['filename'] = $info->getFilename();
                        $inst['isdelete'] = 0;
                        $inst['create_time'] = time();
                        $result =Db::name('mk_importdata')->insert($inst);
                        if($result){
                            array_shift($data);//去掉第一行表头数据
                            $newdata = [];
                            foreach ($data as $k=>$v){
                                foreach ($v as $key=>$value){
                                    $keys = \PHPExcel_Cell::stringFromColumnIndex($key);
                                    $add[$keys] = $value;
                                }
                                $newdata[] = $add;
                            }
                            //dump($newdata);die;
                            $result = Db::table($excdatatable)->insertAll($newdata);
                            if(!$result){
                                Db::rollback();
                            }
                        }else{
                            Db::rollback();
                        }
                        //dump($result);die;
                    }else{
                        Db::rollback();
                    }
                    Db::commit();
                    unlink($path);//删除文件
                }
                $end = microtime(true);//开启代码运行时间
                $msg  = '上传成功：耗时' . round($end - $start, 3) . '秒' . "\n";
                $msg  .= 'use memory:' . memory_get_usage() . "\n";
                return msg('1', $path, $msg);
            } else {
                return msg('-1', '', $file->getError());
            }
        }
    }
    public function abcd($data){
        foreach ($data as $key=>$value){
            $keys = \PHPExcel_Cell::stringFromColumnIndex($key);
            yield $add[$keys] = $value;
        }
    }
    /**
     * 下载导入与模板
     * @author: pan
     * @Date: times
     */
    public function downExcelTemplate(){
        require_once(EXTEND_PATH.'/PHPExcel/Classes/PHPExcel.php');
        $param = request()->param();
        $columnslist = json_decode($param['data'],true);
        $tablename = $param['tablename'];
        $database = config('database.database');    //数据库库名
        $prefix = config('database.prefix');    //数据库表扩展名
        $tablename = $prefix.$tablename;
        $sql = "select COLUMN_NAME,COLUMN_COMMENT,DATA_TYPE from information_schema.columns where table_schema = '$database' and table_name = '$tablename' ";
        $tableinnfo = Db::query($sql);
        $objPHPExcel = new \PHPExcel();
        $n=0;
        //设置表头
        foreach ($tableinnfo as $key=>$val){
//            $name = trim($val['COLUMN_COMMENT']);
//            if($val['COLUMN_NAME']=='id'){
//                $name = '序号';
//            }
            if(array_key_exists($val['COLUMN_NAME'],$columnslist)){
                $name = $columnslist[$val['COLUMN_NAME']];
                $beginCol = \PHPExcel_Cell::stringFromColumnIndex($n).(1);
                $objPHPExcel->getActiveSheet()->setCellValue($beginCol, $name);
                $n++;
            }
        }
        $fileName = iconv("utf-8", "gb2312", '模板表.xlsx');
        //重命名表
        ob_end_clean();//清除缓冲区,避免乱码
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="'.$fileName.'"');
        header('Cache-Control: max-age=0');
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $objWriter->save('php://output');
        exit;
    }
    /**
     * 有表导入 excel
     */
    public function tableImoprt(){
        require_once(EXTEND_PATH.'/PHPExcel/Classes/PHPExcel.php');
        setlocale(LC_ALL, 'zh_CN.UTF-8');//解决 pathinfo 中文乱码的问题
        $start = microtime(true);//开启代码运行时间
        $extlist = 'xlsx,xls,csv';
        if (request()->isAjax()) {
            $file = request()->file('file');
            $param = request()->param();
            if($param){
                if(!empty($param['tablename'])){
                    $tablename = $param['tablename'];
                }else{
                    return msg('-1', '', '导入失败：表不存在');
                }
            }else{
                return msg('-1', '', '导入失败');
            }
            $oldFileinfo = $file->getInfo();
            $oldFileNameinfo =$oldFileinfo['name'];
            $fileinfo = pathinfo($oldFileNameinfo);
            $fileExt = $fileinfo['extension'];   //文件扩展
            $basename = $fileinfo['basename'];   //带扩展的文件名
            $oldfilename = $fileinfo['filename'];//不带扩展文件名
            $info = $file->validate(['ext' =>$extlist ])->move(ROOT_PATH . 'public' . DS . 'upload'.'/'.'excle');
            if ($info) {
                $path =  './upload' .'/'.'excle'. '/' . date('Ymd') . '/' . $info->getFilename();//文件保存路径
                if(empty($path) OR !file_exists($path)) {
                    die('file not exists!');
                }
                if ($fileExt=='xlsx') {
                    $type='Excel2007';
                }elseif($fileExt=='xls') {
                    $type = 'Excel5';
                }else{
                    $type = 'CSV';
                }
                //echo $fileExt;die;
                $objReader = \PHPExcel_IOFactory::createReader($type);//判断使用哪种格式
                if(!$objReader->canRead($path)){//判断是否是其他文档修改后缀的excel
                    die('No Excel!');
                }
                $objReader ->setReadDataOnly(true); //只读取数据,会智能忽略所有空白行,这点很重要.
                if( $fileExt=='xlsx' || $fileExt=='xls'){
                    $objPHPExcel = $objReader->load($path);
                    $data = $objPHPExcel->getSheet()->toArray();
                }
                elseif($fileExt=='csv') {
                    $objReader =$objReader->setDelimiter(',')
                        ->setInputEncoding('GBK')
                        ->setEnclosure('"')
                        ->setLineEnding("\r\n")
                        ->setSheetIndex(0);
                    $objPHPExcel = $objReader->load($path);
                    $data = $objPHPExcel->getSheet()->toArray();
                }
                //array_shift($data);//去掉第一行表头数据
                if($data){
                    $database = config('database.database');    //数据库库名
                    $prefix = config('database.prefix');    //数据库表扩展名
                    $tablename = $prefix.$tablename;
                    $sql = "select COLUMN_NAME,COLUMN_COMMENT,DATA_TYPE from information_schema.columns where table_schema = '$database' and table_name = '$tablename' ";
                    $tableinnfo = Db::query($sql);
                    foreach ($tableinnfo as $key=>$val){
                        //$fieldarrp[] = trim($val['COLUMN_NAME']);
                        if(trim($val['COLUMN_NAME'])!='id' && trim($val['COLUMN_NAME'])!='create_time'
                            && trim($val['COLUMN_NAME'])!='update_time' &&trim($val['COLUMN_NAME'])!='isdelete'){
                            $fieldarrp[] = trim($val['COLUMN_NAME']);
                        }
                    }
                    $header = $data[0];
                    $Colname=[];
                    $n=0;
                    foreach ($header as $k=>$vs){
                        //判断每一列是否是时间类型的,时间类型，下面需要进行转换
                        if(strpos($vs,'时间') || strpos($vs,'日期')){
                            $Colname[] = \PHPExcel_Cell::stringFromColumnIndex($k);//$K 转换成 0->A
                            $n++;
                        }
                    }
                    $columnslist = json_decode($param['data'],true);
                    Db::startTrans();
                    if($tableinnfo){
                        array_shift($data);//去掉第一行表头数据
                        $newdata = [];
                        $field = explode(',',$param['field']);
                        $title = explode(',',$param['title']);
                        foreach ($data as $k=>$v){
                            foreach ($header as $key=>$value){
                                if($value!='序号'){
                                    $keys = \PHPExcel_Cell::stringFromColumnIndex($key);
                                    $fieledkey = array_keys($columnslist,$value)[0];//返回键,也就是对应的数据表字段
                                    $fieldlist = array_column($tableinnfo, 'COLUMN_NAME');//返回数组中指定的一列
                                    $tablfieledkey = array_keys($fieldlist,$fieledkey);//返回键
                                    $fieldtype = $tableinnfo[$tablfieledkey[0]]['DATA_TYPE'];//返回字段类型
                                    $add['isdelete'] = 0;
                                    if(is_float($v[$key])){//判断是否是浮点类型
                                        if($Colname){
                                            if(in_array($keys,$Colname)){
                                                //PHPexxcel 读取时间长度会变成浮点类型，所以这里需要进行转换
                                                $data=$v[$key];//从excel导入后的时间
                                                $d = 25569; //excel和php之间相差的时间
                                                $t = 24 * 60 * 60;//一天24小时 echo gmdate('Y-m-d H:i:s', (data - d) *t);
                                                //时间类型判断
                                                if(gmdate('Y', ($data - $d) *$t) == '1970' ){
                                                    $add[$fieledkey] = null;
                                                }else{
                                                    if($fieldtype=='datetime'){
                                                        $add[$fieledkey] = gmdate('Y-m-d H:i:s', ($data - $d) *$t);
                                                    }
                                                    if($fieldtype=='date'){
                                                        $add[$fieledkey] = gmdate('Y-m-d', ($data - $d) *$t);
                                                    }
                                                    if($fieldtype=='time'){
                                                        $add[$fieledkey] = gmdate('H:i:s', ($data - $d) *$t);
                                                    }
                                                    if($fieldtype=='timestamp'){
                                                        $add[$fieledkey] = gmdate('Ymd His', ($data - $d) *$t);
                                                    }
                                                    if($fieldtype=='year'){
                                                        $add[$fieledkey] = gmdate('Y', ($data - $d) *$t);
                                                    }
                                                    if($fieldtype=='int'){
                                                        $add[$fieledkey] = strtotime(gmdate('Y-m-d H:i:s', ($data - $d) *$t+1));
                                                    }
                                                }
                                            }else{
                                                $add[$fieledkey] = $v[$key];
                                            }
                                        }else{
                                            $add[$fieledkey] = $v[$key];
                                        }
                                    }else{
                                        $add[$fieledkey] = $v[$key];
                                    }
                                }
                            }
                            $newdata[] = $add;
                        }
                        //dump($newdata);die;
                        $result = Db::table($tablename)->insertAll($newdata);
                        if(!$result){
                            $msg = "导入失败";
                            $code = -1;
                            Db::rollback();
                        }else{
                            $msg = "导入成功";
                            $code = 1;
                        }
                    }else{
                        $msg = "导入失败";
                        $code = -1;
                        Db::rollback();
                    }
                    Db::commit();
                    unlink($path);//删除文件
                }else{
                    $msg = "导入失败:没有数据";
                    $code = -1;
                }
                $end = microtime(true);//开启代码运行时间
//                $msg  = '耗时' . round($end - $start, 3) . '秒' . "\n";
//                $msg  .= 'use memory:' . memory_get_usage() . "\n";
                return msg($code, $path, $msg);
            } else {
                return msg('-1', '', $file->getError());
            }
        }
    }
}
